package ctrl;

import gui.CollectionTabbedPane;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.List;

import org.jdom2.Document;
import org.jdom2.Element;
import org.jdom2.JDOMException;
import org.jdom2.input.SAXBuilder;

import util.Constants;
import util.Constants.EVEData;
import data.EVEItem;
import data.EVESystem;
import data.ItemSystemCollection;

public class DBAccess {

  private Connection _connection;

  public DBAccess() {
    try {
      Class.forName("org.hsqldb.jdbcDriver");
    }
    catch (ClassNotFoundException e) {
      e.printStackTrace();
    }
  }

  public void _initializeDatabase() {
    _connect();
//     _execute("DROP TABLE IF EXISTS users_items");
//     _execute("DROP TABLE IF EXISTS users_systems");
//     _execute("DROP TABLE IF EXISTS users");
//     _execute("DROP TABLE IF EXISTS items");
//     _execute("DROP TABLE IF EXISTS systems");
//     _execute("DROP TABLE IF EXISTS configuration");

    _execute("CREATE TABLE IF NOT EXISTS users ( " + "user_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, " + "username VARCHAR(20) NOT NULL);");
    _execute("CREATE TABLE IF NOT EXISTS items ( " + "item_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY," + "eve_item_id VARCHAR(8) NOT NULL," + "eve_item_name VARCHAR(80) NOT NULL );");
    _execute("CREATE TABLE IF NOT EXISTS systems ( " + "system_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, " + "eve_system_id VARCHAR(8) NOT NULL," + "eve_system_name VARCHAR(20) NOT NULL );");
    _execute("CREATE TABLE IF NOT EXISTS users_items ( " + "user_id INT REFERENCES users(user_id), " + "item_id INT REFERENCES items(item_id));");
    _execute("CREATE TABLE IF NOT EXISTS users_systems ( " + "user_id INT REFERENCES users(user_id), " + "system_id INT REFERENCES systems(system_id));");
    _execute("CREATE TABLE IF NOT EXISTS configuration (filename VARCHAR(50) NOT NULL, UPDATE_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL);");

    try {
      ResultSet result = _executeQuery("SELECT * FROM configuration;");
      Timestamp itemStoredTiemstamp = null;
      Timestamp systemStoredTiemstamp = null;
      while (result.next()) {
        if(result.getString(1).equals(Constants.ItemIDFile)){
          itemStoredTiemstamp = result.getTimestamp(2);
        }
        else if(result.getString(1).equals(Constants.SystemIDFile)){
          systemStoredTiemstamp = result.getTimestamp(2);
        }
      }
      result.close();
      
      File itemIDFile = new File(Constants.ItemIDFile);
      File systemIDFile = new File(Constants.SystemIDFile);
      Timestamp itemActualTiemstamp = new Timestamp(itemIDFile.lastModified());
      Timestamp systemActualTiemstamp = new Timestamp(systemIDFile.lastModified());
      
      boolean fillItemData = false;
      if(itemStoredTiemstamp != null){
        if(itemStoredTiemstamp.before(itemActualTiemstamp)){
          fillItemData = true;
          _updateData("UPDATE configuration SET UPDATE_DATE = CURRENT_TIMESTAMP WHERE filename = '" + Constants.ItemIDFile + "';");
        }
      }
      else {
        fillItemData = true;
        _updateData("INSERT INTO configuration (filename) VALUES ('" + Constants.ItemIDFile + "');");
      }
      if(fillItemData){
        System.out.println("creating item data");
        _fillItemsFromTXTFile(Constants.EVEData.Items, new BufferedReader(new InputStreamReader(new FileInputStream(Constants.ItemIDFile))));
      }
      
      boolean fillSystemData = false;
      if(systemStoredTiemstamp != null){
        if(systemStoredTiemstamp.before(systemActualTiemstamp)){
          fillSystemData = true;
          _updateData("UPDATE configuration SET UPDATE_DATE = CURRENT_TIMESTAMP WHERE filename = '" + Constants.SystemIDFile + "';");
        }
      }
      else {
        fillSystemData = true;
        _updateData("INSERT INTO configuration (filename) VALUES ('" + Constants.SystemIDFile + "');");
      }
      if(fillSystemData){
        System.out.println("creating system data");
        _fillSystemsFromXMLFile(Constants.EVEData.Systems, Constants.SystemIDFile);
      }
    }
    catch (FileNotFoundException | SQLException e1) {
      e1.printStackTrace();
    }
    finally {
      _disconnect();
    }
  }

  public void _connect() {
    try {
      _connection = DriverManager.getConnection("jdbc:hsqldb:file:EVE_Trading_Tool_DB; shutdown=true", "sa", "");
    }
    catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public void _disconnect() {
    try {
      _connection.close();
    }
    catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public void _execute(String query) {
    try {
      java.sql.Statement sqlStatement = _connection.createStatement();
      boolean isResultSet = sqlStatement.execute(query);
      System.out.println(query + " is ResultSet: " + isResultSet);
      sqlStatement.close();
    }
    catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public void _updateData(String query) {
    try {
      java.sql.Statement sqlStatement = _connection.createStatement();
      sqlStatement.executeUpdate(query);
      sqlStatement.close();
    }
    catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public ResultSet _executeQuery(String query) {
    ResultSet result = null;
    try {
      java.sql.Statement sqlStatement = _connection.createStatement();
      result = sqlStatement.executeQuery(query);
      sqlStatement.close();
    }
    catch (SQLException e) {
      e.printStackTrace();
    }
    return result;
  }

  private void _fillItemsFromTXTFile(EVEData data, BufferedReader fileReader) {
    try {
      String line = "";
      line = fileReader.readLine();
      while (line != null) {
        if (!line.isEmpty() && Character.isDigit(line.charAt(0))) {
          _insert(data, line.substring(0, 11).trim(), line.substring(12));
        }
        line = fileReader.readLine();
      }
      fileReader.close();
    }
    catch (IOException e) {
      e.printStackTrace();
    }
  }

  private void _fillSystemsFromXMLFile(EVEData data, String file) {
    try {
      Document doc = new SAXBuilder().build(file);
      List<Element> rows = doc.getRootElement().getChild("result").getChild("rowset").getChildren("row");
      for (Element row : rows) {
        _insert(data, row.getAttributeValue("solarSystemID"), row.getAttributeValue("solarSystemName"));
      }
    }
    catch (JDOMException | IOException e) {
      e.printStackTrace();
    }
  }

  private void _insert(EVEData data, String id, String name) {
    String table = Constants._eveDataToString(data);
    String column2 = "";
    String column3 = "";
    if (data.equals(Constants.EVEData.Items)) {
      column2 = "eve_item_id";
      column3 = "eve_item_name";
    }
    if (data.equals(Constants.EVEData.Systems)) {
      column2 = "eve_system_id";
      column3 = "eve_system_name";
    }

    try {
      String query = "INSERT INTO " + table + " (" + column2 + "," + column3 + ") VALUES (?,?)";
      PreparedStatement prepStatement = _connection.prepareStatement(query);
      prepStatement.setString(1, id);
      prepStatement.setString(2, name);
      prepStatement.execute();
      prepStatement.close();
    }
    catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public void _storeCurrentUser(CollectionTabbedPane collectionPane) {
    _connect();

    ItemSystemCollection selectedCollection = collectionPane.getSelectedCollection();
    String userName = selectedCollection.getUserName();
    String query = "";

    System.out.println("storing collection " + userName + " with ID " + selectedCollection.getUserPrimaryKey());

    if (selectedCollection.getUserPrimaryKey().equals("unknown")) {
      query = "INSERT INTO users (username) VALUES ('" + userName + "');";
      _updateData(query);

      try {
        query = "SELECT user_id FROM users WHERE username = '" + userName + "';";
        ResultSet result = _executeQuery(query);
        while (result.next()) {
          Integer storedUserID = result.getInt(1);
          selectedCollection.setUserPrimaryKey(storedUserID.toString());
        }
        result.close();
      }
      catch (SQLException e1) {
        e1.printStackTrace();
      }
    }

    System.out.println("stored collection " + selectedCollection.getUserName() + " with ID " + selectedCollection.getUserPrimaryKey());
    int userID = Integer.valueOf(selectedCollection.getUserPrimaryKey());

    for (EVEItem item : selectedCollection.getItems()) {
      int itemID = Integer.valueOf(item.getItemPrimaryKey());
      boolean exists = false;

      try {
        query = "SELECT item_id FROM users_items WHERE item_id = " + itemID + " and user_id = " + userID + ";";
        ResultSet result = _executeQuery(query);
        while (result.next()) {
          if (result.getInt(1) == itemID) {
            exists = true;
          }
        }
        result.close();
      }
      catch (SQLException e1) {
        e1.printStackTrace();
      }

      if (!exists) {
        query = "INSERT INTO users_items VALUES (" + userID + ", " + itemID + ");";
        _updateData(query);
      }
    }
    for (EVEItem item : selectedCollection.getDeletedItems()) {
      query = "DELETE FROM users_items WHERE user_id = " + userID + " AND item_id = " + item.getItemPrimaryKey() + ";";
      _updateData(query);
      System.out.println("deleted " + item.getItemName() + " from " + selectedCollection.getUserName());
    }

    for (EVESystem system : selectedCollection.getSystems()) {
      int systemID = Integer.valueOf(system.getSystemPrimaryKey());
      boolean exists = false;

      try {
        query = "SELECT system_id FROM users_systems WHERE system_id = " + systemID + " and user_id = " + userID + ";";
        ResultSet result = _executeQuery(query);
        while (result.next()) {
          if (result.getInt(1) == systemID) {
            exists = true;
          }
        }
        result.close();
      }
      catch (SQLException e1) {
        e1.printStackTrace();
      }

      if (!exists) {
        query = "INSERT INTO users_systems VALUES (" + userID + ", " + systemID + ");";
        _updateData(query);
      }
    }
    for (EVESystem system : selectedCollection.getDeletedSystems()) {
      query = "DELETE FROM users_systems WHERE user_id = " + userID + " AND system_id = " + system.getSystemPrimaryKey() + ";";
      _updateData(query);
      System.out.println("deleted " + system.getSystemName() + " from " + selectedCollection.getUserName());
    }

    _disconnect();
  }

  public void _dumpUserTables() {
    try {
      java.sql.Statement sqlStatementItems = _connection.createStatement();
      String queryItems = "SELECT * FROM users_items;";
      ResultSet resultItems = sqlStatementItems.executeQuery(queryItems);
      while (resultItems.next()) {
        System.out.println("Items: " + resultItems.getString(1) + " : " + resultItems.getString(2));
      }
      resultItems.close();
      sqlStatementItems.close();
      java.sql.Statement sqlStatementSystems = _connection.createStatement();
      String querySystems = "SELECT * FROM users_systems;";
      ResultSet resultSystems = sqlStatementSystems.executeQuery(querySystems);
      while (resultSystems.next()) {
        System.out.println("Systems: " + resultSystems.getString(1) + " : " + resultSystems.getString(2));
      }
      resultSystems.close();
      sqlStatementSystems.close();
    }
    catch (SQLException e) {
      e.printStackTrace();
    }
  }
}
